# -*- coding: UTF-8 -*-
# Author: Damon(wuud1)
# CreateDate: 2018-08-10
# Message: 舍弗勒网站零件抓取,根据pid查询.断点续爬.网站特性: 一次搜索请求分成了三次,第一次post请求,返回redirect标签,get请求标签内
# url,返回302重定向,获取location字段的url,再get请求进入最终详情页面, 也可以传入allow_redirects=True,让其自动处理302重定向

import requests
import redis
import os
import sys
import hashlib
import pymysql
import json
from lxml import etree
import time
from collections import OrderedDict

# 解决python2编码问题
reload(sys)
sys.setdefaultencoding('utf-8')


class SchaefflerForPid(object):
    def __init__(self, **kwargs):
        self.db_config = kwargs.get("db_config")  # 数据库配置
        self.select_table = kwargs.get("select_table")  # 查询表名
        self.insert_table = kwargs.get("insert_table")  # 插入表名
        self.partBrand = kwargs.get("partBrand")  # 网站品牌
        self.redis_set = '{}_set'.format(kwargs.get('partBrand'))  # redis数据库集合名,用来存储查询过的pid
        self.conn1 = None  # 数据库链接和游标
        self.conn2 = None
        self.curs1 = None
        self.curs2 = None
        # self.base_path = '/Users/duanmengdi/Desktop/workspace/{}_html/'.format(kwargs.get("partBrand"))  # 存储html文件的路径
        self.base_path = os.getcwd() + '/../{}_html/'.format(self.partBrand)  # 获取当前路径并拼接出存储html文件的路径
        if not os.path.exists(self.base_path):  # 如果路径不存在,就创建一个
            os.mkdir(self.base_path)
        self.location = ""  # 重定向链接
        self.total_num = 0  # 全局计数变量
        self.sub_num = 0  # 全局备用计数变量
        self.is_sleep = kwargs.get("is_sleep", True)  # 是否控制速度的开关变量
        self.r = redis.Redis(host='localhost', port=6379, db=0).pipeline()  # redis链接,用于断点续爬


        # 爬虫所需的参数

        self.javax_value = ""
        self.headers = {
            "Host": "webcat.schaeffler.com",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36",
        }

        self.post_headers = {
            "Host": "webcat.schaeffler.com",
            "Faces-Request": "partial/ajax",
            "Origin": "https://webcat.schaeffler.com",
            "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/67.0.3396.99 Safari/537.36",
            "Referer": "https://webcat.schaeffler.com/web/schaeffler/zh_CN/index.xhtml?reset=1",
            "Content-Type": "application/x-www-form-urlencoded;charset=UTF-8",
            # "": "",
        }

        self.cookies = {
            "JSESSIONID": "3ACD417AAE82F77FFFA10AA656CA5A29",
            "mode": "HOME",
            "locale": "zh_CN",
            "emos_jcsid": "AWUimvs9uld1fQMhWLmqfAB0IpvGH*zT:1:AWUimvs7Mi36bls9bWvVW_1P3KiX0Az_:1533883906875",
            "emos_jcvid": "AWUimvs9uld1fQMhWLmqfAB0IpvGH*zT:1:AWUimvs9uld1fQMhWLmqfAB0IpvGH*zT:1533883906875:0:true:1",
            "_ga": "GA1.2.633283001.1533883908",
            "_gid": "GA1.2.920774674.1533883908",
        }

        self.post_data = OrderedDict()
        self.post_data["searchByKeywords:form"] = "searchByKeywords:form"
        self.post_data["[configId]"] = "schaeffler"
        self.post_data["locale"] = "zh_CN"
        self.post_data["[ds]"] = "LG2"
        # self.post_data["[from]"] = "1721735593"  # 或许无用
        self.post_data["searchByKeywords:input"] = ""
        self.post_data["javax.faces.ViewState"] = ""
        self.post_data["javax.faces.source"] = "searchByKeywords:start"
        self.post_data["javax.faces.partial.event"] = "click"
        self.post_data["javax.faces.partial.execute"] = "searchByKeywords:start searchByKeywords:form"
        self.post_data["javax.faces.partial.render"] = "searchByKeywords:input userMessages"
        self.post_data["javax.faces.behavior.event"] = "action"
        self.post_data["javax.faces.partial.ajax"] = "true"

        self.start_url = 'https://webcat.schaeffler.com/web/schaeffler/zh_CN/index.xhtml?reset=1'
        self.search_post_url = 'https://webcat.schaeffler.com/web/public/index.xhtml'
        self.base_url = 'https://webcat.schaeffler.com'

    def get_md5(self, str):
        '''获取md5加密电子指纹'''
        md5 = hashlib.md5()
        md5.update(str)
        return md5.hexdigest()

    def get_conn(self, dbconfig_dict):
        '''获取数据库链接'''
        conn = pymysql.connect(**dbconfig_dict)
        return conn

    def get_cursor(self, conn, type='stream'):
        '''获取数据库游标'''
        if type == 'stream':
            return conn.cursor(pymysql.cursors.SSCursor)  # 返回流式游标,查询大量数据时不占用内存(返回数据形式是元组)
        elif type == 'dict':
            return conn.cursor(pymysql.cursors.DictCursor)  # 返回字典形式游标,查询出的数据以字典形式返回
        else:
            raise Exception("cursor type error")

    def construct_insert_sql(self, table, data, db=None):
        '''传入表名, 数据字典, 返回insert语句'''
        fields = '`' + '`,`'.join(data.keys()) + '`'
        values = []
        for v in data.values():
            if v == 0:
                v = '0'
            elif not v:
                v = ''
            if type(v) == int or type(v) == long:
                values.append(v.__str__())
            elif v == "now()":
                values.append(v)
            else:
                values.append("'%s'" % v.replace("'", " ").replace("\\", "\\\\"))
        if db:
            sql = 'INSERT INTO `%s`.`%s` (%s) VALUES (%s)' % (db, table, fields, ",".join(values))
        else:
            sql = 'INSERT INTO `%s` (%s) VALUES (%s)' % (table, fields, ",".join(values))
        return sql

    def parse_url(self, url, method, post_data=None, headers=None, tofile=True, **kwargs):
        requests.packages.urllib3.disable_warnings()  # 因为把ssl安全验证关了,所以不让代码报不安全的https请求警告
        pid = kwargs.get("local_pid", "")
        charset = kwargs.get("charset", "utf8")  # 暂时没有用到
        allow_redirects = kwargs.get("allow_redirects", False)  # 是否允许重定向, 默认不重定向,获取原始响应
        if method == 'GET':
            data = "GET" + url + pid  # 拼接data
            md5 = self.get_md5(data)  # 获取此次请求电子指纹
            file_path = self.base_path + md5 + ".html"
            if os.path.exists(file_path):  # 判断文件是否存在,如果存在就从本地读取
                print "GET for local"
                with open(file_path, 'r') as f:
                    res = f.read()
                response = res
            else:  # 如果路径不存在, 从web端请求
                print "GET for web"
                try:
                    response = requests.get(url=url, verify=False, headers=headers, cookies=self.cookies, timeout=30,
                                            allow_redirects=allow_redirects)
                except Exception as e:
                    response = requests.get(url=url, verify=False, headers=headers, cookies=self.cookies, timeout=30,
                                            allow_redirects=allow_redirects)

                # 每次请求都获取cookies, 并更新self.cookies(如果获取到cookies的话)
                cookies = response.cookies.get_dict()
                for k in cookies:
                    self.cookies[k] = cookies[k]

                # 获取location,
                location = response.headers.get("Location", None)
                if location:
                    print "响应头中存在location"
                    self.location = location
                    print location
                    # return response.content.decode('utf8')

                if response:
                    if tofile:
                        with open(file_path, 'w') as f:  # 保存此次请求的html
                            f.write(response.content.decode('utf8'))
                    response = response.content.decode("utf8")
                else:
                    response = None

        elif method == "POST":
            data = "POST" + url + json.dumps(post_data)
            md5 = self.get_md5(data)
            file_path = self.base_path + md5 + ".html"
            if os.path.exists(file_path):  # 判断文件是否存在,如果存在就从本地读取
                print "POST for local"
                with open(file_path, 'r') as f:
                    res = f.read()
                response = res
            else:
                print "POST for web"
                try:
                    response = requests.post(url=url, data=post_data, verify=False, headers=headers,
                                             cookies=self.cookies, timeout=30,
                                             allow_redirects=allow_redirects)
                except Exception as e:
                    response = requests.post(url=url, data=post_data, verify=False, headers=headers,
                                             cookies=self.cookies, timeout=30,
                                             allow_redirects=allow_redirects)

                # 获取cookies, 并更新self.cookies
                cookies = response.cookies.get_dict()
                for k in cookies:
                    self.cookies[k] = cookies[k]

                # 获取location,
                location = response.headers.get("Location", None)
                if location:
                    print "响应头中存在location"
                    self.location = location
                    print location
                    # response, file_path = self.parse_url(url=location, method="GET", headers=headers, local_pid=pid)
                    # print "已重新请求location地址"
                    # return response, file_path
                    # return response.content.decode('utf8')

                if response:
                    if tofile:
                        with open(file_path, 'w') as f:  # 保存此次请求的html
                            f.write(response.content.decode('utf8'))
                    response = response.content.decode("utf8")

                else:
                    response = None

        return response, file_path

    def r_reset(self):
        self.r.delete(self.redis_set)
        self.r.save()

    def get_data(self, sql):
        self.curs1.execute(sql)
        for data in self.curs1:
            local_pid = data[1]
            # local_pid = 'A0119973692'  # 测试用pid, 有数据
            brand_name = data[0]
            # 断点续爬模块
            self.r.sismember(self.redis_set, local_pid)
            if self.r.execute()[0]:
                continue
            self.r.sadd(self.redis_set, local_pid)

            # 控制速度
            if self.is_sleep:
                time.sleep(1)

            try:
                start_response, start_file_path = self.parse_url(url=self.start_url, method="GET", headers=self.headers, local_pid=local_pid)
            except Exception as e:
                print e
                start_response, start_file_path = self.parse_url(url=self.start_url, method="GET", headers=self.headers, local_pid=local_pid)

            if start_response:
                start_html = etree.HTML(start_response)
            else:
                print local_pid, start_file_path
                raise Exception("没有获取到首页响应")

            self.javax_value = start_html.xpath('//input[@name="javax.faces.ViewState"]/@value')
            for i in self.javax_value:
                if i:
                    self.javax_value = i
                    break

            # print self.javax_value
            self.post_data["searchByKeywords:input"] = local_pid
            self.post_data["javax.faces.ViewState"] = self.javax_value

            try:
                search_post_response, search_post_file_path = self.parse_url(url=self.search_post_url, method="POST", post_data=self.post_data, headers=self.post_headers, local_pid=local_pid)
            except Exception as e:
                print e
                search_post_response, search_post_file_path = self.parse_url(url=self.search_post_url, method="POST", post_data=self.post_data, headers=self.post_headers, local_pid=local_pid)

            if search_post_response:
                search_post_html = etree.HTML(bytes(search_post_response))
            else:
                print local_pid, search_post_file_path
                raise Exception("没有获取到搜索post请求响应")

            redirect_url = search_post_html.xpath('//redirect/@url')
            redirect_url = self.base_url+redirect_url[0] if len(redirect_url) else ""
            print redirect_url

            try:
                detail_response, detail_file_path = self.parse_url(url=redirect_url, method="GET", headers=self.headers, local_pid=local_pid, allow_redirects=True)
            except Exception as e:
                print e
                detail_response, detail_file_path = self.parse_url(url=redirect_url, method="GET", headers=self.headers, local_pid=local_pid, allow_redirects=True)

            if detail_response:
                detail_html = etree.HTML(detail_response)
            else:
                print local_pid, detail_file_path
                raise Exception("没有获取到详情页面的响应")

            part_title = detail_html.xpath('//h1[@class="anteros"]/text()')
            part_title = part_title[0] if len(part_title)>0 else ""
            # print part_title
            if not part_title:
                print "没有找到零件title,跳过", local_pid, detail_file_path
                continue

            part_name = part_title.split(' ')[-1].replace(' ', '').replace('\n', '').replace('\t', '').replace('\r', '')
            part_num = part_title.split(' ')[-2].replace(' ', '').replace('\n', '').replace('\t', '').replace('\r', '')
            # print part_name, part_num
            status = detail_html.xpath(u'//span[text()="商品状态"]/../following-sibling::td[1]//text()')
            status = status[0] if len(status)>0 else ""
            # print status
            tr_list = detail_html.xpath('//table[contains(@id, "productDetailPage:product-cards:crossreference")]//tr')
            if len(tr_list) <= 0:
                print "没有找到oe对照列表", local_pid, detail_file_path
                continue

            for tr in tr_list:
                brandname1 = tr.xpath('./td[1]/div/text()')
                brandname1 = brandname1[0].replace(' ', '').replace('\n', '').replace('\t', '') if len(brandname1)>0 else ""
                pid1 = tr.xpath('./td[2]/div/div/text()')
                pid1 = pid1[0].replace(' ', '').replace('\n', '').replace('\t', '') if len(pid1)>0 else ""
                # print brandname1, pid1

                last_data = dict(
                    pid=local_pid,
                    brand_name=brand_name,
                    part_brand=self.partBrand,
                    part_num=part_num,
                    part_name=part_name,
                    brand_name1=brandname1,
                    pid1=pid1,
                    status=status
                )
                print last_data

                insert_sql = self.construct_insert_sql(table=self.insert_table, data=last_data)
                # print insert_sql
                # exit()
                self.curs2.execute(insert_sql)
                self.r.execute()
                self.total_num += 1
                if self.total_num % 100 == 0:
                    print "提交insert缓存, 当前数量: {}".format(self.total_num)
                    self.conn2.commit()

    def run(self):
        # 获取一系列数据库链接,游标
        self.conn1 = self.get_conn(dbconfig_dict=self.db_config)
        self.conn2 = self.get_conn(dbconfig_dict=self.db_config)
        self.curs1 = self.get_cursor(conn=self.conn1)
        self.curs2 = self.get_cursor(conn=self.conn2)

        sql = '''select brand,pid from {}'''.format(self.select_table)
        try:
            self.get_data(sql)
        except Exception as e:
            self.conn2.commit()
            self.r.execute()
            print "出现异常, 已提交缓存数据"
            raise e

        self.conn2.commit()
        print "最终提交, 总数据量: {}".format(self.total_num)

        self.curs1.close()
        self.curs2.close()
        self.conn1.close()
        self.conn2.close()


if __name__ == '__main__':
    db_config = dict(
        host="127.0.0.1",
        port=3306,
        user="root",
        passwd="123456",
        db="my_data",
        charset="utf8"
    )
    partBrand = 'schaeffler'  # 网站品牌  舍弗勒
    is_sleep = False  # 控制速度开关
    select_table = '0810_pid'  # 查询表
    insert_table = 'schaeffler_all'  # 插入表
    tools = SchaefflerForPid(db_config=db_config, partBrand=partBrand, is_sleep=is_sleep, select_table=select_table,
                          insert_table=insert_table)
    # tools.r_reset()  # 重置断点续爬数据, 及时关闭
    tools.run()  # 启动








